package com.y.report.hepler;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Session;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.db.handler.EntityListHandler;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.log.level.Level;
import cn.hutool.setting.Setting;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.util.PropertyElf;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;


/**
 * HikariData for jdbc
 * <p>
 * g
 */
public class JdbcHelper {

    private static Logger log = LoggerFactory.getLogger(JdbcHelper.class);

    private DataSource datasource;
    private int initRetry;

    /**
     * @param prop required [driverClassName,jdbcUrl,username,password]
     */
    public JdbcHelper(Properties prop, Level loglevel) {
        HikariConfig hikariConfig = new HikariConfig(prop);
        //default jdbc pool settins############################################################
        //等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒 -->
        hikariConfig.setConnectionTimeout(30000);
        // 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟 -->
        hikariConfig.setIdleTimeout(60000);
        //一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，建议设置比数据库超时时长少30秒，参考MySQL wait_timeout参数（show variables like '%timeout%';） -->        hikariDataSource.setMinimumIdle(minimumIdle);
        hikariConfig.setMinimumIdle(60000);

        //连接池中允许的最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count)
        hikariConfig.setMaximumPoolSize(8);
        //连接池中允许的最小连接数
        hikariConfig.setMinimumIdle(1);
        //default jdbc pool settings ############################################################

        PropertyElf.setTargetFromProperties(hikariConfig, prop);

        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
        log.info("init JdbcHelper hikariDataSource:{}", hikariDataSource.getDataSourceProperties());

        final String per = "=__jdbcHelper";
        final Setting setting = Setting.create();
        prop.entrySet()
                .stream()
                .filter(x -> Objects.nonNull(x.getKey()))
                .filter(x -> Objects.nonNull(x.getValue()))
                .filter(x -> x.getKey().toString().startsWith(per))
                .forEach(x -> setting.set(x.getKey().toString(), x.getValue().toString()));

        DbUtil.setShowSqlGlobal(setting);
        if (loglevel == null)
            loglevel = Level.TRACE;
        if (setting.size() == 0)
            DbUtil.setShowSqlGlobal(true, false, true, loglevel);
        this.initRetry = Integer.parseInt(prop.getOrDefault(per + ".restry", "3").toString());
        this.datasource = hikariDataSource;
    }

    public static List<Map<String, Object>> execSql(DataSource DataSource, String sql, List<Object> sqlArgs, boolean isQuery, boolean isTransaction) throws SQLException {
        DataSource ds = DataSource;
        Session session = null;
        if (CollUtil.isEmpty(sqlArgs)) sqlArgs = ListUtil.empty();
        List<Map<String, Object>> result;
        try {
            session = DbUtil.newSession(ds);
            if (isQuery) {
                log.trace("execSql => sql:{}, sqlArgs:{}, isQuery:{}, isTransaction:{}", sql, sqlArgs, isQuery, isTransaction);
                result = session.query(sql, sqlArgs.toArray())
                        .stream()
                        .map(x -> x.entrySet()
                                .stream()
                                .<Map<String, Object>>collect(LinkedHashMap::new, (x1, x2) -> x1.put(x2.getKey(), x2.getValue()), Map::putAll)
                        )
                        .collect(Collectors.toList());
            } else {
                if (isTransaction)
                    session.beginTransaction();
                result = ListUtil.of(MapUtil.of("count", session.execute(sql, sqlArgs.toArray())));
                if (isTransaction)
                    session.commit();
            }
        } catch (SQLException throwables) {
            if (Objects.nonNull(session)) {
                session.close();
                if (isTransaction)
                    session.rollback();

            }
            throw new RuntimeException(throwables);
        }
        log.trace("execSql => sql:{}, sqlArgs:{}, isQuery:{}, isTransaction:{}, result:{}...", sql, sqlArgs, isQuery, isTransaction, CollUtil.isNotEmpty(result) ? result.stream().limit(1).collect(Collectors.toList()) : result);
        return result;
    }

    public static List<Map<String, Object>> execSql(Properties prop, String sql, List<Object> sqlArgs, boolean isQuery, boolean isTransaction) throws SQLException {
        DataSource ds = new SimpleDataSource(
                prop.getProperty("jdbcUrl"),
                prop.getProperty("username"),
                prop.getProperty("password"),
                prop.getProperty("driverClassName")
        );
        return execSql(ds, sql, sqlArgs, isQuery, isTransaction);
    }

    /**
     * @param type    [1 query, 2exec, 3 exec GeneratedKey]
     * @param sql
     * @param sqlArgs
     * @throws SQLException
     */
    public List<Map<String, Object>> sqlRunner(int type, String sql, List<Object> sqlArgs) throws SQLException {
        int retry = this.initRetry;
        log.trace("exec sqlRunner type:{}, sql:{}, sqlArgs:{}", type, sql, sqlArgs);
        Connection conn = null;
        List<Map<String, Object>> resultList = null;
        final String k = "count";
        do {
            try {
                conn = this.datasource.getConnection();
                resultList = Collections.emptyList();
                switch (type) {
                    case 1:
                        resultList = SqlExecutor.query(conn, sql, new EntityListHandler(), sqlArgs.toArray())
                                .stream()
                                .map(x -> x.entrySet()
                                        .stream()
                                        .<Map<String, Object>>collect(LinkedHashMap::new, (x1, x2) -> x1.put(x2.getKey(), x2.getValue()), Map::putAll)
                                )
                                .collect(Collectors.toList());
                        break;
                    case 2:
                        resultList.add(Collections.singletonMap(k, SqlExecutor.execute(conn, sql, sqlArgs)));
                        break;
                    case 3:
                        resultList.add(Collections.singletonMap(k, SqlExecutor.executeForGeneratedKey(conn, sql, sqlArgs)));
                        break;
                }
            } catch (Exception e) {
                log.error("exec sqlRunner err, current retry number:{}, sql:{}, sqlArgs:{}, err msg:{}", retry - 1, sql, sqlArgs, e.toString());
                if ((--retry) == 0)
                    throw new SQLException(e);
                try {
                    TimeUnit.SECONDS.sleep(5 * (initRetry - retry));
                } catch (InterruptedException interruptedException) {
                    interruptedException.printStackTrace();
                    log.error("sleep err:{}", interruptedException.getMessage());
                }
            } finally {
                if (conn != null)
                    conn.close();
            }
        } while (retry > 0);

        log.debug("exec sqlRunner size:{}, fields:{}", resultList, Objects.requireNonNull(resultList).isEmpty() ?
                Collections.emptyList() : resultList.size() == 1 && resultList.get(0).containsKey(k) ?
                resultList.get(0).get(k) : resultList.get(0).keySet());
        return resultList;
    }


    /**
     * @param sql
     * @param sqlArgs
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> query(String sql, List<Object> sqlArgs) throws SQLException {
        int retry = this.initRetry;
        log.trace("exec query sql:{}, sqlArgs:{}", sql, sqlArgs);
        List<Map<String, Object>> resultList = Collections.emptyList();
        do {
            try (Session session = Session.create(this.datasource)) {
                resultList = session
                        .query(sql, sqlArgs.toArray())
                        .stream()
                        .map(x -> x.entrySet()
                                .stream()
                                .<Map<String, Object>>collect(LinkedHashMap::new, (x1, x2) -> x1.put(x2.getKey(), x2.getValue()), Map::putAll)
                        )
                        .collect(Collectors.toList());
                retry = 0;
            } catch (Exception e) {
                e.printStackTrace();
                log.error("exec query err, current retry number:{}, sql:{}, sqlArgs:{}, err msg:{}", retry - 1, sql, sqlArgs, e.getMessage());
                if ((--retry) == 0)
                    throw new SQLException(e);
                try {
                    TimeUnit.SECONDS.sleep(5 * (initRetry - retry));
                } catch (InterruptedException interruptedException) {
                    interruptedException.printStackTrace();
                    log.error("sleep err:{}", interruptedException.getMessage());
                }
            }
        } while (retry > 0);
        log.debug("exec query sql size:{}, fields:{}", resultList, resultList.isEmpty() ? Collections.emptyList() : resultList.get(0).keySet());
        return resultList;
    }


    /**
     * @param sql
     * @param sqlArgs
     * @param returnKey
     * @param isTransaction
     * @return
     * @throws SQLException
     */
    public long exec(String sql, List<Object> sqlArgs, boolean returnKey, boolean isTransaction) throws SQLException {
        int retry = this.initRetry;
        log.trace("exec sql:{}, sqlArgs:{}, returnKey：{}, returnKey：{}", sql, sqlArgs, returnKey, isTransaction);
        Session session = null;
        Long count = 0L;
        do {
            try {
                session = Session.create(this.datasource);
                if (isTransaction)
                    session.beginTransaction();
                count = null;
                log.debug("exec sql:{}, sqlArgs:{}", sql, sqlArgs);
                if (returnKey)
                    count = session.executeForGeneratedKey(sql, sqlArgs.toArray());
                else {
                    DbUtil.setReturnGeneratedKeyGlobal(false);
                    count = (long) session.execute(sql, sqlArgs.toArray());
                }
                if (isTransaction)
                    session.commit();

                retry = 0;
            } catch (Exception e) {
                log.error("exec exec err, current retry number:{}, sql:{}, sqlArgs:{}, err msg:{}", retry - 1, sql, sqlArgs, e.toString());
                if (isTransaction)
                    try {
                        if (session != null)
                            session.rollback();
                    } catch (SQLException e2) {
                        e2.printStackTrace();
                    }
                if ((--retry) == 0)
                    throw new SQLException(e);
                try {
                    TimeUnit.SECONDS.sleep(5 * (initRetry - retry));
                } catch (InterruptedException interruptedException) {
                    interruptedException.printStackTrace();
                    log.error("sleep err:{}", interruptedException.getMessage());
                }
            } finally {
                if (session != null)
                    session.close();
            }
        } while (retry > 0);
        log.debug("exec sql result:{}", count);
        return Objects.isNull(count) ? 0L : count;
    }

    public void close() {
        try {
            DbUtil.close(this.datasource);
        } catch (Exception e) {
            log.warn(e.getMessage());
        }
    }

    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.put("driverClassName", "com.mysql.jdbc.Driver");
        properties.put("jdbcUrl", "jdbc:mysql://10.192.147.1:9030/tpcds");
        properties.put("username", "root");
        properties.put("password", "root");
//        properties.put("maximumPoolSize", 3);
        JdbcHelper helper = new JdbcHelper(properties, null);
//        long count = helper.exec("INSERT INTO bi4bus.test_table (id, name, desc, etl_time) VALUES (?,?,?,?)", Arrays.asList(3, "sz11", "AA-1", "2020-12-12 11:12:21", "2020-12-12 11:12:21"), false, false);
//        System.out.println(count);
        List<Map<String, Object>> query = helper.query("select now()", Collections.emptyList());
        System.out.println(query);
    }

}
